﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using FineUI;
using System.Reflection;
using NPOI.HSSF.Util;
using System.ComponentModel;

namespace SOA.Common
{
    public static class ExcelHelper
    {
        /// <summary>
        /// 导出Excel文档
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columnDic">表头（string,string;DataField,HeaderText)</param>
        /// <param name="list"></param>
        /// <param name="sheetName">表格名</param>
        /// <param name="fileName">文件名</param>
        public static void GridToExcel<T>(Dictionary<string, string> columnDic, List<T> list, string sheetName = "", string fileName = "")
        {
            // 当前上下文
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            string myFileName = "MyExcelFile";
            if (!string.IsNullOrEmpty(fileName))
            {
                myFileName = fileName;
            }
            #region NPOI 导出

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);

            #region 单元格样式
            // 生成一个字体1
            IFont font = book.CreateFont();
            font.IsBold = true;

            // 生成一个字体2
            IFont font2 = book.CreateFont();
            font2.Color = (short)FontColor.Red;

            ICellStyle style = book.CreateCellStyle();
            style.SetFont(font);

            ICellStyle style2 = book.CreateCellStyle();
            style2.SetFont(font2);

            // 日期格式
            ICellStyle style12 = book.CreateCellStyle();
            IDataFormat format = book.CreateDataFormat();
            style12.DataFormat = format.GetFormat("yyyy-MM-dd");

            // 数值格式(默认保留两位小数)
            ICellStyle style4 = book.CreateCellStyle();
            IDataFormat format4 = book.CreateDataFormat();
            style4.DataFormat = format4.GetFormat("0.000");

            // 边框设置
            ICellStyle s67 = book.CreateCellStyle();

            s67.BorderBottom = BorderStyle.Thin;
            s67.BottomBorderColor = HSSFColor.Black.Index;
            s67.BorderLeft = BorderStyle.Thin;
            s67.LeftBorderColor = HSSFColor.Black.Index;
            s67.BorderRight = BorderStyle.Thin;
            s67.RightBorderColor = HSSFColor.Black.Index;
            s67.BorderTop = BorderStyle.Thin;
            s67.TopBorderColor = HSSFColor.Black.Index;
            s67.Alignment = HorizontalAlignment.Center;
            s67.VerticalAlignment = VerticalAlignment.Center;

            #endregion

            if (columnDic.Count > 0)
            {

                #region 表头
                NPOI.SS.UserModel.IRow rowheader = sheet.CreateRow(0);

                int h = 0;

                foreach (KeyValuePair<string, string> kv in columnDic)
                {
                    rowheader.CreateCell(h).SetCellValue(kv.Value);
                    h++;
                }


                #endregion

                #region 表主体
                Type type = typeof(T);

                int i = 0;
                foreach (T model in list)
                {
                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);

                    int n = 0;
                    foreach (KeyValuePair<string, string> kv in columnDic)
                    {
                        ICell cell = row.CreateCell(n);
                        // 字段类型
                        //string headerToolTip = column.HeaderToolTip;

                        string _fieldName = kv.Key;
                        object _value = null;

                        PropertyInfo _aa = type.GetProperty(_fieldName);

                        if (_aa != null)
                        {
                            _value = _aa.GetValue(model);

                            string _fieldType = _aa.PropertyType.Name;  // 字段类型


                            if (_value == null)
                            {
                                cell.SetCellValue("");
                            }
                            else
                            {
                                switch (_fieldType)
                                {
                                    case "Int32":
                                        cell.SetCellValue(int.Parse(_value.ToString()));
                                        break;
                                    case "Decimal":
                                        cell.SetCellValue(double.Parse(_value.ToString()));
                                        cell.CellStyle = style4;
                                        break;
                                    case "Boolean":
                                        cell.SetCellValue(Boolean.Parse(_value.ToString()));
                                        break;
                                    case "DateTime":
                                        cell.SetCellValue(DateTime.Parse(_value.ToString()));
                                        cell.CellStyle = style12;
                                        break;
                                    default: //String
                                        cell.SetCellValue(_value.ToString());
                                        //cell.CellStyle = style;
                                        break;
                                }
                            }
                        }
                        else
                        {
                            // 字段不存在
                            cell.SetCellValue("");
                        }


                        n++;
                    }

                    i++;
                }
                #endregion
            }

            // ...

            // 写入到客户端  
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", myFileName));
            curContext.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();

            #endregion
        }


        /// <summary>
        /// Excel To DataTable
        /// </summary>
        /// <param name="filePath">绝对路径</param>
        /// <param name="sheetName">SheetName</param>
        /// <returns></returns>
        public static DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
        {

            DataTable dt = new DataTable();

            if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
            {

                #region .xls文件处理:HSSFWorkbook
                HSSFWorkbook hssfworkbook = null;
                try
                {

                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }

                catch (Exception e)
                {
                    throw e;
                }


                ISheet sheet = hssfworkbook.GetSheet(sheetName);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);

                //一行最后一个方格的编号 即总的列数  

                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    //SET EVERY COLUMN NAME

                    HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());

                }

                while (rows.MoveNext())
                {
                    IRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    if (row.RowNum == 0) continue;//The firt row is title,no need import
                    for (int i = 0; i < row.LastCellNum; i++)
                    {

                        if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
                        {
                            break;
                        }

                        ICell cell = row.GetCell(i);

                        if (cell != null)
                        {
                            if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
                            {
                                break;
                            }

                            if (cell == null)
                            {
                                dr[i] = null;
                            }
                            else
                            {
                                dr[i] = cell.ToString();
                            }
                        }

                    }

                    dt.Rows.Add(dr);

                }

                #endregion
            }
            else
            {
                #region .xlsx文件处理:XSSFWorkbook
                XSSFWorkbook hssfworkbook = null;
                try
                {
                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }

                }
                catch (Exception e)
                {
                    throw e;
                }

                ISheet sheet = hssfworkbook.GetSheet(sheetName);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);

                //一行最后一个方格的编号 即总的列数  

                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    //SET EVERY COLUMN NAME
                    XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());
                }



                while (rows.MoveNext())
                {
                    IRow row = (XSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    if (row.RowNum == 0) continue;//The firt row is title,no need import
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
                        {
                            break;

                        }

                        ICell cell = row.GetCell(i);

                        if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
                        {
                            break;
                        }

                        if (cell == null)
                        {

                            dr[i] = null;

                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }

                    }

                    dt.Rows.Add(dr);
                }

                #endregion
            }

            return dt;
        }


        /// <summary>
        /// 读取Excel 表名
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<String> GetExcelSheet(string filePath)
        {
            List<string> strList = new List<string>();
            if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
            {

                #region .xls文件处理:HSSFWorkbook
                HSSFWorkbook hssfworkbook = null;
                try
                {

                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }

                catch (Exception e)
                {
                    throw e;
                }

                for (int i = 0; i < hssfworkbook.Workbook.NumSheets; i++)
                {
                    //获取每个Sheet表
                    strList.Add(hssfworkbook.Workbook.GetSheetName(i));

                }
                #endregion
            }
            else
            {

                #region .xlsx文件处理:XSSFWorkbook
                XSSFWorkbook hssfworkbook = null;
                try
                {
                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }

                }
                catch (Exception e)
                {
                    throw e;
                }

                for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
                {
                    //获取每个Sheet表
                    strList.Add(hssfworkbook.GetSheetName(i));
                }

                #endregion

            }

            return strList;
        }

        /// <summary>
        /// NPOI导出Excel文档
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="fileName"></param>
        public static void DataTableToExcel(DataTable dt, string sheetName = "", string fileName = "")
        {
            // 当前上下文
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            string myFileName = "MyExcelFile";
            if (!string.IsNullOrEmpty(fileName))
            {
                myFileName = fileName;
            }


            #region NPOI 导出

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);

            if (dt != null)
            {
                int cols = dt.Columns.Count;

                #region 表头
                NPOI.SS.UserModel.IRow rowheader = sheet.CreateRow(0);
                //if (sheetHeader.Length == 0)
                //{
                for (int j = 0; j < cols; j++)
                {
                    rowheader.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
                }
                //}
                //else
                //{
                //    for (int j = 0; j < sheetHeader.Length; j++)
                //    {
                //        rowheader.CreateCell(j).SetCellValue(sheetHeader[j]);
                //    }
                //}
                #endregion

                // 表主体数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);

                    for (int j = 0; j < cols; j++)
                    {
                        row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
            }

            // ...

            // 写入到客户端  
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", myFileName));
            curContext.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();

            #endregion


        }

        /// <summary>
        /// Grid使用NPOI导出Excel文档
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="fileName"></param>
        public static void GridToExcel(Grid Grid1, string sheetName = "", string fileName = "")
        {
            // 当前上下文
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            string myFileName = "MyExcelFile";
            if (!string.IsNullOrEmpty(fileName))
            {
                myFileName = fileName;
            }


            #region NPOI 导出

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);

            if (Grid1 != null)
            {
                List<GridColumn> listFrom = Grid1.Columns.Where(m => m.GetType().Name == "BoundField" && m.Hidden == false).ToList();
                int cols = listFrom.Count();

                #region 表头
                NPOI.SS.UserModel.IRow rowheader = sheet.CreateRow(0);

                int h = 0;

                foreach (GridColumn column in listFrom)
                {
                    rowheader.CreateCell(h).SetCellValue(column.HeaderText);
                    h++;
                }


                #endregion

                int r = 0;
                foreach (GridRow drow in Grid1.Rows)
                {
                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(r + 1);

                    int n = 0; //Excel 的列
                    string value;
                    foreach (GridColumn column in listFrom)
                    {
                        // 字段类型
                        string headerToolTip = column.HeaderToolTip;
                        value = drow.Values[column.ColumnIndex].ToString();
                        switch (headerToolTip)
                        {
                            case "int":
                                row.CreateCell(n).SetCellValue(int.Parse(value));
                                break;
                            case "float":
                                row.CreateCell(n).SetCellValue(float.Parse(value));
                                break;
                            case "decimal":
                                row.CreateCell(n).SetCellValue((double)decimal.Parse(value));
                                break;
                            default:
                                row.CreateCell(n).SetCellValue(value);
                                break;
                        }

                        n++;
                    }

                    r++;
                }
            }

            // ...

            // 写入到客户端  
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", myFileName));
            curContext.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();

            #endregion


        }

        /// <summary>
        /// Grid使用NPOI导出Excel文档(DataTable)
        /// </summary>
        /// <param name="Grid1"></param>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="fileName"></param>
        public static void GridToExcel(Grid Grid1, DataTable dt, string sheetName = "", string fileName = "")
        {
            // 当前上下文
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            string myFileName = "MyExcelFile";
            if (!string.IsNullOrEmpty(fileName))
            {
                myFileName = fileName;
            }


            #region NPOI 导出

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);

            ICellStyle style11 = book.CreateCellStyle();
            style11.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

            if (Grid1 != null)
            {
                int cols = Grid1.Columns.Count;

                #region 表头
                NPOI.SS.UserModel.IRow rowheader = sheet.CreateRow(0);

                int j = 0;
                foreach (GridColumn column in Grid1.Columns)
                {
                    if (!column.Hidden && column.HeaderToolTip != "hidden")
                    {
                        rowheader.CreateCell(j).SetCellValue(column.HeaderText);
                        j++;
                    }

                }

                #endregion

                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);

                    int m = 0;
                    int n = 0;
                    foreach (GridColumn column in Grid1.Columns)
                    {
                        ICell cell = row.CreateCell(n);
                        // 字段类型
                        string headerToolTip = column.HeaderToolTip;
                        if (!column.Hidden && column.HeaderToolTip != "hidden")
                        {
                            string strField = dr[((FineUI.BoundField)(column)).DataField.ToString()].ToString();

                            if (string.IsNullOrEmpty(strField))
                            {
                                cell.SetCellValue("");
                            }
                            else
                            {
                                switch (headerToolTip)
                                {
                                    case "int":
                                        cell.SetCellValue(int.Parse(dr[((FineUI.BoundField)(column)).DataField.ToString()].ToString()));
                                        break;
                                    case "float":
                                        cell.SetCellValue(float.Parse(dr[((FineUI.BoundField)(column)).DataField.ToString()].ToString()));
                                        cell.CellStyle = style11;
                                        break;
                                    case "decimal":
                                        cell.SetCellValue((double)decimal.Parse(dr[((FineUI.BoundField)(column)).DataField.ToString()].ToString()));
                                        break;
                                    case "hidden":
                                        cell.SetCellValue("");
                                        break;
                                    default:
                                        cell.SetCellValue(dr[((FineUI.BoundField)(column)).DataField.ToString()].ToString());
                                        break;
                                }
                            }
                            n++;
                        }
                        m++;
                    }

                    i++;
                }
            }

            // ...

            // 写入到客户端  
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", myFileName));
            curContext.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();

            #endregion


        }

        /// <summary>
        /// Grid使用NPOI导出Excel文档(List<T>)
        /// GridToExce<T>(Grid1, list)
        /// </summary>
        /// <param name="Grid1"></param>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="fileName"></param>
        public static void GridToExcel<T>(Grid Grid1, List<T> list, string sheetName = "", string fileName = "")
        {
            // 当前上下文
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            string myFileName = "MyExcelFile";
            if (!string.IsNullOrEmpty(fileName))
            {
                myFileName = fileName;
            }


            #region NPOI 导出

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);

            ICellStyle style11 = book.CreateCellStyle();
            style11.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

            if (Grid1 != null)
            {

                List<GridColumn> listFrom = Grid1.Columns.Where(m => m.GetType().Name == "BoundField" && m.Hidden == false).ToList();
                int cols = listFrom.Count();

                #region 表头
                NPOI.SS.UserModel.IRow rowheader = sheet.CreateRow(0);

                int h = 0;
                foreach (GridColumn column in listFrom)
                {
                    rowheader.CreateCell(h).SetCellValue(column.HeaderText);
                    h++;
                }

                //NPOI.SS.UserModel.IRow row = sheet.CreateRow(1);

                //GridColumn tempColumn = null;
                //Type tempColumnType = null;

                //for (int j = 0; j < cols; j++)
                //{
                //    tempColumn = Grid1.Columns[j];
                //    tempColumnType = tempColumn.GetType();

                //    rowheader.CreateCell(j).SetCellValue(tempColumnType.Name);                
                //}

                #endregion

                #region 表主体
                Type type = typeof(T);

                int i = 0;
                foreach (T model in list)
                {
                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);

                    int n = 0;
                    foreach (GridColumn column in listFrom)
                    {
                        ICell cell = row.CreateCell(n);
                        // 字段类型
                        string headerToolTip = column.HeaderToolTip;

                        string _fieldName = ((FineUI.BoundField)(column)).DataField.ToString();
                        object _value = null;

                        PropertyInfo _aa = type.GetProperty(_fieldName);
                        if (_aa != null)
                        {
                            _value = _aa.GetValue(model);
                        }

                        //foreach (PropertyInfo propertyInfo in type.GetProperties())
                        //{
                        //    //获取属性名
                        //    string name = propertyInfo.Name;

                        //    if (name == _fieldName)
                        //    {
                        //        //获取属性值
                        //        _value = propertyInfo.GetValue(model);
                        //        break;
                        //    }
                        //}

                        if (_value == null || string.IsNullOrEmpty(_value.ToString()))
                        {
                            cell.SetCellValue("");
                        }
                        else
                        {
                            switch (headerToolTip)
                            {
                                case "int":
                                    cell.SetCellValue(int.Parse(_value.ToString()));
                                    break;
                                case "float":
                                    var temp1 = float.Parse(_value.ToString());
                                    cell.SetCellValue(temp1);
                                    cell.CellStyle = style11;
                                    break;
                                case "decimal":
                                    var temp2 = double.Parse(_value.ToString());
                                    cell.SetCellValue(temp2);
                                    break;
                                case "hidden":
                                    cell.SetCellValue("");
                                    break;
                                default:
                                    cell.SetCellValue(_value.ToString());
                                    break;
                            }
                        }
                        n++;
                    }

                    i++;
                }
                #endregion
            }

            // ...

            // 写入到客户端  
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", myFileName));
            curContext.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();

            #endregion


        }

        /// <summary>
        /// 创建表头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        public static ISheet CreateHeader<T>(this ISheet sheet, Dictionary<string, string> dic = null)
        {
            IRow fristRow = sheet.CreateRow(0);
            if (dic == null)
            {
                Type type = typeof(T);
                PropertyInfo[] propertyInfoArray = type.GetProperties();

                for (int i = 0; i < propertyInfoArray.Length; i++)
                {
                    PropertyInfo item = propertyInfoArray[i];
                    string desStr = "";
                    DescriptionAttribute des = item.GetCustomAttribute<DescriptionAttribute>();
                    if (des != null)
                    {
                        desStr = des.Description;
                    }
                    else
                    {
                        desStr = item.Name;
                    }

                    ICell cell = fristRow.CreateCell(i, CellType.String);
                    cell.SetCellValue(desStr);
                }
            }
            else
            {
                int i = 0;
                foreach (KeyValuePair<string, string> item in dic)
                {
                    string field = item.Key;
                    string fieldDes = item.Value;
                    ICell cell = fristRow.CreateCell(i, CellType.String);
                    cell.SetCellValue(fieldDes);
                    i++;
                }
            }

            return sheet;
        }

        /// <summary>
        /// 填充Sheet
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="list"></param>
        public static void FillSheet<T>(this ISheet sheet, IEnumerable<T> list, Dictionary<string, string> dic = null)
        {
            IWorkbook workbook = sheet.Workbook;
            ICellStyle style1 = SetCellStyle(workbook, "0.00");
            ICellStyle style2 = SetCellStyle(workbook, "yyyy年m月d日");

            Type type = typeof(T);
            PropertyInfo[] propertyInfoArray = type.GetProperties();

            int r = 1;

            if (dic == null)
            {
                foreach (var model in list)
                {
                    IRow row = sheet.CreateRow(r);
                    for (int i = 0; i < propertyInfoArray.Length; i++)
                    {
                        PropertyInfo prop = propertyInfoArray[i];
                        object value = prop.GetValue(model);
                        //string _fieldType = prop.PropertyType.Name;  // 字段类型
                        TypeCode code = Type.GetTypeCode(prop.PropertyType);
                        SetCellValue(row, i, value, code, style1, style2);
                    }
                    r++;
                }
            }
            else
            {
                foreach (var model in list)
                {
                    IRow row = sheet.CreateRow(r);
                    int i = 0;
                    foreach (KeyValuePair<string, string> item in dic)
                    {
                        PropertyInfo property = type.GetProperty(item.Key);

                        if (property != null)
                        {
                            object value = property.GetValue(model);
                            //string _fieldType = property.PropertyType.Name;  // 字段类型
                            TypeCode code = Type.GetTypeCode(property.PropertyType);

                            SetCellValue(row, i, value, code, style1, style2);
                        }
                        else
                        {
                            //ICell cell = fristRow.CreateCell(i, CellType.String);
                            //cell.SetCellValue("");
                        }
                        i++;
                    }
                    r++;
                }
            }
        }

        /// <summary>
        /// 设置列样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="formatStr"></param>
        /// <returns></returns>
        private static ICellStyle SetCellStyle(IWorkbook workbook, string formatStr)
        {
            ICellStyle style = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            style.DataFormat = format.GetFormat(formatStr);
            return style;
        }

        /// <summary>
        /// 设置列值
        /// </summary>
        /// <param name="fristRow"></param>
        /// <param name="i"></param>
        /// <param name="value"></param>
        /// <param name="_fieldType"></param>
        private static void SetCellValue(IRow row, int i, object value, TypeCode code, ICellStyle style1, ICellStyle style2)
        {
            ICell cell = row.CreateCell(i);
            switch (code)
            {
                case TypeCode.Int32:
                    cell.SetCellValue(Convert.ToInt32(value));
                    break;
                case TypeCode.Decimal:
                    cell.SetCellValue(Convert.ToDouble(value));
                    cell.CellStyle = style1;
                    break;
                case TypeCode.Boolean:
                    cell.SetCellValue(Convert.ToBoolean(value));
                    break;
                case TypeCode.DateTime:
                    cell.SetCellValue(Convert.ToDateTime(value));
                    cell.CellStyle = style2;
                    break;
                default:
                    cell.SetCellValue(Convert.ToString(value));
                    break;
            }
        }
    }

}
